package com.atguigu.flow.utils;


import org.apache.commons.io.IOUtils;
import org.apache.poi.ss.usermodel.*;
        import org.apache.poi.xssf.usermodel.XSSFWorkbook;

        import java.io.FileInputStream;
        import java.io.FileOutputStream;
        import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class ExcelDataFiller {

    public static void fillExistingSheetWithData(String templateFilePath, String outputFilePath, List<List<String>> data,
                                                 String imagePath, int colIndex, int rowIndex) {
        try (FileInputStream fileIn = new FileInputStream(templateFilePath);
             Workbook workbook = new XSSFWorkbook(fileIn)) {

            // 获取Sheet1
            Sheet sheet = workbook.getSheetAt(0);

            // 填充数据
            int startRow = 1; // 假设从第二行开始填充数据
            for (int i = 0; i < data.size(); i++) {
                Row row = sheet.getRow(startRow + i);
                if (row == null) {
                    row = sheet.createRow(startRow + i);
                }
                List<String> rowData = data.get(i);
                for (int j = 0; j < rowData.size(); j++) {
                    Cell cell = row.getCell(j);
                    if (cell == null) {
                        cell = row.createCell(j);
                    }
                    cell.setCellValue(rowData.get(j));
                }
            }
            try (FileInputStream imageIn = new FileInputStream(imagePath)) {
                byte[] imageBytes = IOUtils.toByteArray(imageIn);
                int pictureIdx = workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_JPEG);

                CreationHelper helper = workbook.getCreationHelper();
                Drawing<?> drawing = sheet.createDrawingPatriarch();

                ClientAnchor anchor = helper.createClientAnchor();
                anchor.setCol1(colIndex); // 图片起始列（从0开始）
                anchor.setRow1(rowIndex); // 图片起始行（从0开始）
                anchor.setCol2(colIndex + 3); // 图片结束列
                anchor.setRow2(rowIndex + 3); // 图片结束行

                Picture picture = drawing.createPicture(anchor, pictureIdx);
                picture.resize();
            }

            // 输出到文件
            try (FileOutputStream fileOut = new FileOutputStream(outputFilePath)) {
                workbook.write(fileOut);
            }

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        List<List<String>> data = new ArrayList<>();

        // 使用Arrays.asList()创建内部列表
        data.add(Arrays.asList("张三", "25", "男"));
        data.add(Arrays.asList("李四", "30", "女"));
        data.add(Arrays.asList("王五", "28", "男"));

        String templateFilePath = "D:\\test1.xlsx"; // 替换为你的模板文件路径
        String outputFilePath = "D:\\output.xlsx";
        String imagepath="D:\\intersky\\前程\\公司logo图片\\格联公司章.jpg";
        int colIndex = 2; // Excel中的第二列（B列），在POI中是1
        int rowIndex = 5;

        fillExistingSheetWithData(templateFilePath, outputFilePath, data,imagepath, colIndex, rowIndex);
        System.out.println("输出到"+outputFilePath+"成功");
    }
}


